package vip.xumy.core.utils;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import lombok.extern.log4j.Log4j2;
import vip.xumy.core.exception.CoreException;
import vip.xumy.core.promise.ExcelParser;

/**	All rights reserved
 *	author:mengyxu
 *	date:2019年11月22日
 */

@Log4j2
@SuppressWarnings("all")
public class ExcelUtil {
	
	
	/**
	 * 	将数据转换为Excel表格，并将Excel表格转为字节数组返回
	 * @param template 	模版路径
	 * @param data		源数据
	 * @param parser	数据转换工具
	 * @return
	 * @throws CoreException
	 */
	public static byte[] parseToExcelByteArray(String template, List<? extends Object> data, ExcelParser parser) throws CoreException {
		Workbook book = parserToExcel(template, data, parser);
		try(
			ByteArrayOutputStream bos = new ByteArrayOutputStream();
		) {
		    book.write(bos);
		    return bos.toByteArray();
		} catch (IOException e) {
			log.error("将Excel转为字节数组失败",e);
			throw new CoreException("将Excel转为字节数组失败");
		}
	}
	
	/**
	 * 	将数据转换为Excel表格并返回
	 * @param template 	模版路径
	 * @param data		源数据
	 * @param parser	数据转换工具
	 * @return
	 * @throws CoreException
	 */
	public static Workbook parserToExcel(String template, List<? extends Object> data, ExcelParser parser) {
		Workbook book = loadTemplate(template);
		Sheet sheet = book.getSheetAt(0);
		int index = 1;
		for (Object obj : data) {
			List<String[]> result = parser.parseToArray(obj);
			for (String[] arr : result) {
				Row row = sheet.createRow(index++);
				for (int i = 0; i < arr.length; i++) {
					row.createCell(i).setCellValue(arr[i]);
				}
			}
		}
		return book;
	}
	
	private static HSSFWorkbook loadTemplate(String template) {
		File file = new File(template);
		if(file.exists()) {
			try {
				return new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(file)));
			} catch (IOException e) {
				log.warn("读取Excel模版失败："+template,e);
			}
		}
		return new HSSFWorkbook();
	}
	
	public static <T> List<T> readExcelAsBeanList(MultipartFile file, ExcelParser parser) throws CoreException{
		return readExcelAsBeanList(file, parser, null);
	}
	
	public static <T> List<T> readExcelAsBeanList(MultipartFile file, ExcelParser parser, Object param) throws CoreException{
		List<String[]> list = readExcel(file);
		return parser.parseToObj(list,param);
	}
	
	public static List<String[]> readExcel(MultipartFile file) throws CoreException{
		Workbook book = loadExcel(file);
		Sheet sheet = book.getSheetAt(0);
		if(sheet == null || sheet.getLastRowNum() < 1) {
			return null;
		}
		int rowLen = sheet.getLastRowNum();
		List<String[]> list = new ArrayList<>();
		for (int i = 1; i <= rowLen; i++) {
			Row row = sheet.getRow(i);
			if(row == null) {
				continue;
			}
			short cellLen = row.getLastCellNum();
			if(cellLen <= 0) {
				continue;
			}
			String[] arr = new String[cellLen];
			for (int j = 0; j < cellLen; j++) {
				Cell cell = row.getCell(j);
				if(cell != null) {
					cell.setCellType(CellType.STRING);
					arr[j] = cell.getStringCellValue();
				}
			}
			if(StringUtil.isAllEmpty(arr)) {
				continue;
			}
			list.add(arr);
		}
		return list;
	}
	
	public static Workbook loadExcel(MultipartFile file) throws CoreException{
		Workbook book = null;
		try {
			InputStream is = file.getInputStream();
			String name = file.getOriginalFilename();
			if(isExcel2003(name)) {
				book = new HSSFWorkbook(is);
			}else if(isExcel2007(name)){
				book = new XSSFWorkbook(is);
			}
		} catch (Exception e) {
			log.error("读取Excel失败",e);
			throw new CoreException("导入失败，读取文件出错！",e);
		}
		if(book == null) {
			throw new CoreException("导入失败，不支持的文件类型！");
		}
		return book;
	}
	
	 /***
     * 
     * @param 判断文件类型是不是2003版本
     * @return
     */
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    /**
     * 
     * @param 判断文件类型是不是2007版本
     * @return
     */
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

}
